using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

namespace SpringWaterDataAccess
{
    /// <summary>
    /// Summary description for ForumDataAccess.
    /// </summary>
    public class ForumDataAccess : SQLAccess
    {
        private const string DBConenctionKey = "DBConnStr";

        public DataSet GetForums()
        {
	        string storeProcedureName = "sp_getallforums";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
	        return ExecuteReturnCmd(cmd);
        }

        public int getPostNumberOfReplies(int postID, string type, string status) 
        {

	        int totalRecords = 0;

	        DataSet ds = getRepliesByPostID(postID,0,0,
				        out totalRecords, type,status,0,"CreateDate");

	        return totalRecords;
        }


       //Revised to use SQL statement instead of store procedure since sp_DeletePost is longer available
		//JerryC
		public void DeletePost(int postID) 
        {
	        //string storeProcedureName = "sp_DeletePost";
			string sSQL = "Delete From ForumPosts Where (PostID = @PostID Or ParentPostID = @PostID)";
	        SqlCommand cmd = new SqlCommand(sSQL);
	        cmd.CommandType = CommandType.Text;
	        //cmd.CommandText = storeProcedureName;

	        SqlParameter idPara = cmd.CreateParameter();
	        idPara.ParameterName = "@PostID";
	        idPara.SqlDbType = SqlDbType.Int;
	        idPara.Value = postID;

	        cmd.Parameters.Add(idPara);
	        ExecuteNonReturnCmd(cmd );
        }

            
        public void updatePostBatch(string constant) 
        {
	        string storeProcedureName = "springofwat.sp_InsertPostsBatch";
	        SqlCommand cmd = new SqlCommand();
	        cmd.CommandType = CommandType.StoredProcedure;
	        cmd.CommandText = storeProcedureName;

	        SqlParameter idPara = cmd.CreateParameter();
	        idPara.ParameterName = "@Constant";
	        idPara.SqlDbType = SqlDbType.NVarChar;
	        idPara.Value = constant;

	        cmd.Parameters.Add(idPara);
	        ExecuteNonReturnCmd(cmd );

        }

        public void DeletePostReply(int postID) 
        {
	        string storeProcedureName = "springofwat.sp_DeletePostReply";
	        SqlCommand cmd = new SqlCommand();
	        cmd.CommandType = CommandType.StoredProcedure;
	        cmd.CommandText = storeProcedureName;

	        SqlParameter idPara = cmd.CreateParameter();
	        idPara.ParameterName = "@PostID";
	        idPara.SqlDbType = SqlDbType.Int;
	        idPara.Value = postID;

	        cmd.Parameters.Add(idPara);
	        ExecuteNonReturnCmd(cmd );

            }

        public void IncreaseClicks(int postID) 
        {
	        string storeProcedureName = "sp_IncreaseClicks"; 
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter postIDPara = cmd.CreateParameter();
	        postIDPara.SqlDbType = SqlDbType.Int;
	        postIDPara.ParameterName = "@PostID";
	        postIDPara.Value = postID;
	        cmd.Parameters.Add(postIDPara);

	        ExecuteNonReturnCmd(cmd);
        }


        public void EditPost(int postID, int categoryID, 
			            string subject, string body, string summary, string status)
        {
	        string storeProcedureName = "sp_EditPost";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter postIDPara = cmd.CreateParameter();
	        postIDPara.SqlDbType = SqlDbType.Int;
	        postIDPara.ParameterName = "@PostID";
	        postIDPara.Value = postID;
	        cmd.Parameters.Add(postIDPara);

	        SqlParameter categoryPara = cmd.CreateParameter();
	        categoryPara.SqlDbType = SqlDbType.Int;
	        categoryPara.ParameterName = "@CategoryID";
	        categoryPara.Value = categoryID;
	        cmd.Parameters.Add(categoryPara);

	        SqlParameter subjectPara = cmd.CreateParameter();
	        subjectPara.SqlDbType = SqlDbType.NVarChar;
	        subjectPara.ParameterName = "@Subject";
	        subjectPara.Value = subject;
	        cmd.Parameters.Add(subjectPara);

	        SqlParameter bodyPara = cmd.CreateParameter();
	        bodyPara.SqlDbType = SqlDbType.NVarChar;
	        bodyPara.ParameterName = "@Body";
	        bodyPara.Value = body;
	        cmd.Parameters.Add(bodyPara);

	        SqlParameter summaryPara = cmd.CreateParameter();
	        summaryPara.SqlDbType = SqlDbType.NVarChar;
	        summaryPara.ParameterName = "@Summary";
	        summaryPara.Value = summary;
	        cmd.Parameters.Add(summaryPara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@Status";
			statusPara.Value = status;
			cmd.Parameters.Add(statusPara);


	        ExecuteNonReturnCmd(cmd);
        }

        public void InsertPost(int parentPostID, int categoryID,
            string subject, string body, int userID, 
            string summary, string type, out string forumID)
        {
            string storeProcedureName = "springofwat.sp_InsertPost";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parentPostIDPara = cmd.CreateParameter();
            parentPostIDPara.SqlDbType = SqlDbType.Int;
            parentPostIDPara.ParameterName = "@ParentPostID";
            parentPostIDPara.Value = parentPostID;
            cmd.Parameters.Add(parentPostIDPara);

            SqlParameter categoryPara = cmd.CreateParameter();
            categoryPara.SqlDbType = SqlDbType.Int;
            categoryPara.ParameterName = "@CategoryID";
            categoryPara.Value = categoryID;
            cmd.Parameters.Add(categoryPara);

            SqlParameter subjectPara = cmd.CreateParameter();
            subjectPara.SqlDbType = SqlDbType.NVarChar;
            subjectPara.ParameterName = "@Subject";
            subjectPara.Value = subject;
            cmd.Parameters.Add(subjectPara);

            SqlParameter bodyPara = cmd.CreateParameter();
            bodyPara.SqlDbType = SqlDbType.NVarChar;
            bodyPara.ParameterName = "@Body";
            bodyPara.Value = body;
            cmd.Parameters.Add(bodyPara);

            SqlParameter summaryPara = cmd.CreateParameter();
            summaryPara.SqlDbType = SqlDbType.NVarChar;
            summaryPara.ParameterName = "@Summary";
            summaryPara.Value = summary;
            cmd.Parameters.Add(summaryPara);

            SqlParameter typePara = cmd.CreateParameter();
            typePara.SqlDbType = SqlDbType.NVarChar;
            typePara.ParameterName = "@Type";
            typePara.Value = type;
            cmd.Parameters.Add(typePara);

            SqlParameter userPara = cmd.CreateParameter();
            userPara.SqlDbType = SqlDbType.Int;
            userPara.ParameterName = "@UserID";
            userPara.Value = userID;
            cmd.Parameters.Add(userPara);

            SqlParameter forumIDPara = cmd.CreateParameter();
            forumIDPara.SqlDbType = SqlDbType.Int;
            forumIDPara.Direction = ParameterDirection.Output;
            forumIDPara.ParameterName = "@ForumID";
            cmd.Parameters.Add(forumIDPara);

            ExecuteNonReturnCmd(cmd);
            forumID = cmd.Parameters["@ForumID"].Value.ToString();
            return;
        }
		//A temperary solution for inserting a new post form ForumPostEdit form. --JerryC
		public void InsertPost(string sInsert) 
		{
			SqlCommand cmd = new SqlCommand(sInsert);
			cmd.CommandType = CommandType.Text;
			
			ExecuteNonReturnCmd(cmd );
		}

        public void InsertPostWithStatus(int parentPostID, int categoryID,
            string status, string filename,
            string subject, string body, int userID, 
            string summary, string type, out string forumID)
        {
            string storeProcedureName = "springofwat.sp_InsertPostWithStatus";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parentPostIDPara = cmd.CreateParameter();
            parentPostIDPara.SqlDbType = SqlDbType.Int;
            parentPostIDPara.ParameterName = "@ParentPostID";
            parentPostIDPara.Value = parentPostID;
            cmd.Parameters.Add(parentPostIDPara);

            SqlParameter categoryPara = cmd.CreateParameter();
            categoryPara.SqlDbType = SqlDbType.Int;
            categoryPara.ParameterName = "@CategoryID";
            categoryPara.Value = categoryID;
            cmd.Parameters.Add(categoryPara);

            SqlParameter statusPara = cmd.CreateParameter();
            statusPara.SqlDbType = SqlDbType.VarChar;
            statusPara.ParameterName = "@Status";
            statusPara.Value = status;
            cmd.Parameters.Add(statusPara);

            SqlParameter filenamePara = cmd.CreateParameter();
            filenamePara.SqlDbType = SqlDbType.VarChar;
            filenamePara.ParameterName = "@URL";
            filenamePara.Value = filename;
            cmd.Parameters.Add(filenamePara);

            SqlParameter subjectPara = cmd.CreateParameter();
            subjectPara.SqlDbType = SqlDbType.NVarChar;
            subjectPara.ParameterName = "@Subject";
            subjectPara.Value = subject;
            cmd.Parameters.Add(subjectPara);

            SqlParameter bodyPara = cmd.CreateParameter();
            bodyPara.SqlDbType = SqlDbType.NVarChar;
            bodyPara.ParameterName = "@Body";
            bodyPara.Value = body;
            cmd.Parameters.Add(bodyPara);

            SqlParameter summaryPara = cmd.CreateParameter();
            summaryPara.SqlDbType = SqlDbType.NVarChar;
            summaryPara.ParameterName = "@Summary";
            summaryPara.Value = summary;
            cmd.Parameters.Add(summaryPara);

            SqlParameter typePara = cmd.CreateParameter();
            typePara.SqlDbType = SqlDbType.NVarChar;
            typePara.ParameterName = "@Type";
            typePara.Value = type;
            cmd.Parameters.Add(typePara);

            SqlParameter userPara = cmd.CreateParameter();
            userPara.SqlDbType = SqlDbType.Int;
            userPara.ParameterName = "@UserID";
            userPara.Value = userID;
            cmd.Parameters.Add(userPara);

            SqlParameter forumIDPara = cmd.CreateParameter();
            forumIDPara.SqlDbType = SqlDbType.Int;
            forumIDPara.Direction = ParameterDirection.Output;
            forumIDPara.ParameterName = "@ForumID";
            cmd.Parameters.Add(forumIDPara);

            ExecuteNonReturnCmd(cmd);
            forumID = cmd.Parameters["@ForumID"].Value.ToString();
            return;
        }

        public void InsertPostReply(string ParentPostID, string CategoryID, 
	        string Subject, string Body, string PostUser, out string ForumID)
        {
	        string storeProcedureName = "springofwat.sp_insertforumpost";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter parentPostIDPara = cmd.CreateParameter();
	        parentPostIDPara.SqlDbType = SqlDbType.Int;
	        parentPostIDPara.ParameterName = "@ParentPostID";
	        if (ParentPostID==null)
		        parentPostIDPara.Value = System.DBNull.Value;
	        else
		        parentPostIDPara.Value = ParentPostID;

	        cmd.Parameters.Add(parentPostIDPara);

	        SqlParameter categoryPara = cmd.CreateParameter();
	        categoryPara.SqlDbType = SqlDbType.Int;
	        categoryPara.ParameterName = "@CategoryID";
	        if (CategoryID==null)
		        categoryPara.Value = System.DBNull.Value;
	        else
		        categoryPara.Value = CategoryID;
	        cmd.Parameters.Add(categoryPara);

	        SqlParameter subjectPara = cmd.CreateParameter();
	        subjectPara.SqlDbType = SqlDbType.NVarChar;
	        subjectPara.ParameterName = "@Subject";
	        subjectPara.Value = Subject;
	        cmd.Parameters.Add(subjectPara);

	        SqlParameter bodyPara = cmd.CreateParameter();
	        bodyPara.SqlDbType = SqlDbType.NVarChar;
	        bodyPara.ParameterName = "@Body";
	        if (Body == null)
		        bodyPara.Value = System.DBNull.Value;
	        else
		        bodyPara.Value = Body;
	        cmd.Parameters.Add(bodyPara);

	        SqlParameter postUserPara = cmd.CreateParameter();
	        postUserPara.SqlDbType = SqlDbType.NVarChar;
	        postUserPara.ParameterName = "@PostUser";
	        postUserPara.Value = PostUser;
	        cmd.Parameters.Add(postUserPara);


	        SqlParameter postIDPara = cmd.CreateParameter();
	        postIDPara.SqlDbType = SqlDbType.Int;
	        postIDPara.Direction = ParameterDirection.Output;
	        postIDPara.ParameterName = "@ForumID";
	        cmd.Parameters.Add(postIDPara);

	        ExecuteNonReturnCmd(cmd);
	        ForumID = cmd.Parameters["@ForumID"].Value.ToString();
	        return;
        }


        public DataSet GetForumCategories(int ForumID)
        {
	        string storeProcedureName = "springofwat.sp_getforumcategories";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

            
	        SqlParameter forumPara = cmd.CreateParameter();
	        forumPara.SqlDbType = SqlDbType.Int;
	        forumPara.ParameterName = "@ForumID";
	        forumPara.Value = ForumID;
	        cmd.Parameters.Add(forumPara);

	        return ExecuteReturnCmd(cmd);
        }

        public void GetPostDetail(string PostID, out string Subject, 
	        out string Body, out string User, out string Date, out string TopicID)
        {
	        string storeProcedureName = "springofwat.sp_getpostdetail";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
            
	        SqlParameter postidPara = cmd.CreateParameter();
	        postidPara.SqlDbType = SqlDbType.Int;
	        postidPara.ParameterName = "@PostID";
	        postidPara.Value = PostID;
	        cmd.Parameters.Add(postidPara);

	        SqlParameter topicidPara = cmd.CreateParameter();
	        topicidPara.SqlDbType = SqlDbType.Int;
	        topicidPara.ParameterName = "@TopicID";
	        topicidPara.Direction = ParameterDirection.Output;
	        cmd.Parameters.Add(topicidPara);

	        DataSet ds = ExecuteReturnCmd(cmd);
	        Subject = ds.Tables[0].Rows[0].ItemArray[1].ToString();
	        Body = ds.Tables[0].Rows[0].ItemArray[2].ToString();
	        Date = ds.Tables[0].Rows[0].ItemArray[3].ToString();
	        User = ds.Tables[0].Rows[0].ItemArray[4].ToString();
	        TopicID = cmd.Parameters["@TopicID"].Value.ToString();
	        return;
        }


        public DataSet GetPostDetailByPostID(string PostID)
        {
	        string storeProcedureName = "springofwat.sp_getpostdetailByPostID";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
            
	        SqlParameter postidPara = cmd.CreateParameter();
	        postidPara.SqlDbType = SqlDbType.Int;
	        postidPara.ParameterName = "@PostID";
	        postidPara.Value = PostID;
	        cmd.Parameters.Add(postidPara);

	        return ExecuteReturnCmd(cmd);
        }

        public DataSet GetBBSPosts(int forumID)
        {
            string storeProcedureName = "dbo.sp_getBBSPosts";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter forumPara = cmd.CreateParameter();
            forumPara.SqlDbType = SqlDbType.Int;
            forumPara.ParameterName = "@ForumID";
            forumPara.Value = forumID;
            cmd.Parameters.Add(forumPara);

            return this.ExecuteReturnCmd(cmd);            
        }
         

        public DataSet GetBBSPostsByUserID(long userID)
        {
            string storeProcedureName = "springofwat.sp_GetBBSPostsByUserID";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter forumPara = cmd.CreateParameter();
            forumPara.SqlDbType = SqlDbType.Int;
            forumPara.ParameterName = "@UserID";
            forumPara.Value = userID;
            cmd.Parameters.Add(forumPara);

            return this.ExecuteReturnCmd(cmd);            
        }



        public DataSet GetForumCategoryPosts(string ForumID, string CategoryID, 
	        string CurrentPage, string StartTopicID, out int TotalTopics, out int TopicsPerPage)
        {
	        TotalTopics = 0;
	        TopicsPerPage = 0;
	        string storeProcedureName = "springofwat.sp_getforumcategoryposts";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter forumPara = cmd.CreateParameter();
	        forumPara.SqlDbType = SqlDbType.Int;
	        forumPara.ParameterName = "@ForumID";
	        if (ForumID==null)
		        forumPara.Value = System.DBNull.Value;
	        else
		        forumPara.Value = ForumID;	
	        cmd.Parameters.Add(forumPara);

	        SqlParameter categoryPara = cmd.CreateParameter();
	        categoryPara.SqlDbType = SqlDbType.Int;
	        categoryPara.ParameterName = "@CategoryID";
	        if (CategoryID==null)
		        categoryPara.Value = System.DBNull.Value;
	        else
		        categoryPara.Value = CategoryID;
	        cmd.Parameters.Add(categoryPara);

	        SqlParameter currPara = cmd.CreateParameter();
	        currPara.SqlDbType = SqlDbType.Int;
	        currPara.ParameterName = "@CurrentPage";
	        if (CurrentPage == null)
		        currPara.Value = System.DBNull.Value;
	        else
		        currPara.Value = CurrentPage;
	        cmd.Parameters.Add(currPara);

	        SqlParameter startPara = cmd.CreateParameter();
	        startPara.SqlDbType = SqlDbType.Int;
	        startPara.ParameterName = "@StartTopicID";
	        if (StartTopicID == null)
		        startPara.Value = System.DBNull.Value;
	        else
		        startPara.Value = StartTopicID;
	        cmd.Parameters.Add(startPara);

	        SqlParameter totalPara = cmd.CreateParameter();
	        totalPara.SqlDbType = SqlDbType.Int;
	        totalPara.ParameterName = "@TotalTopics";
	        totalPara.Direction = ParameterDirection.Output;
	        totalPara.Value = TotalTopics;
	        cmd.Parameters.Add(totalPara);


	        SqlParameter perpagePara = cmd.CreateParameter();
	        perpagePara.SqlDbType = SqlDbType.Int;
	        perpagePara.Direction = ParameterDirection.Output;
	        perpagePara.ParameterName = "@TopicsPerPage";
	        perpagePara.Value = TopicsPerPage;
	        cmd.Parameters.Add(perpagePara);

	        DataSet ds= this.ExecuteReturnCmd(cmd);
	        TotalTopics = int.Parse(cmd.Parameters["@TotalTopics"].Value.ToString());
	        TopicsPerPage = int.Parse(cmd.Parameters["@TopicsPerPage"].Value.ToString());
	        return ds;
        }


        public DataSet getPostsByCategory(int categoryID) 
        {
	        string storeProcedureName = "sp_getCategoryTotalPosts";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
            
	        SqlParameter catidPara = cmd.CreateParameter();
	        catidPara.SqlDbType = SqlDbType.Int;
	        catidPara.ParameterName = "@CategoryID";
	        catidPara.Value = categoryID;
	        cmd.Parameters.Add(catidPara);

	        return ExecuteReturnCmd(cmd);
        }

        public DataSet getPostsByForum(int forumID) 
        {
	        string storeProcedureName = "sp_getForumTotalPosts";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
            
	        SqlParameter postidPara = cmd.CreateParameter();
	        postidPara.SqlDbType = SqlDbType.Int;
	        postidPara.ParameterName = "@ForumID";
	        postidPara.Value = forumID;
	        cmd.Parameters.Add(postidPara);

	        return ExecuteReturnCmd(cmd);
        }


        public DataSet getForumAndCategories(int userID)
        {
	        StringBuilder  strSql = new StringBuilder();

			// if user not logged in, display default forums
	        if (userID == 0) 
	        {
		        strSql.Append("SELECT * from Forums where RoleID = 0 or RoleID = 5");
	        } 
	        else 
	        {
		        strSql.Append("SELECT * from Forums join userroles on forums.roleid = userroles.roleid where userid = " + userID);
	        }

	        strSql.Append("select ForumCategories.ForumID,ForumCategories.CategoryID,ForumCategories.CategoryName,");
	        strSql.Append("count(*) as how_many ");             
            strSql.Append("from ForumCategories, ForumPosts ");
            strSql.Append("where ForumCategories.CategoryID =  ForumPosts.CategoryID and ForumPosts.Type = 'bbs'");
            

	        // We only need to retrieve the forumIDs that exist in the first dataset. Otherwise, the relation
	        // will give errors when this methos is called.
	        if (userID == 0) 
	        {
		        strSql.Append(" and ForumCategories.ForumID in (SELECT forumid from Forums where RoleID = 0 or RoleID = 5) ");
	        } 
	        else 
	        {
		        strSql.Append(" and ForumCategories.ForumID in (SELECT forumid from Forums join userroles on forums.roleid = userroles.roleid where userid = " + userID + ")");
	        }
	        strSql.Append(" group by ForumCategories.ForumID,ForumCategories.CategoryID, ForumCategories.CategoryName");

	        SqlCommand cmd = new SqlCommand(strSql.ToString());
                        
	        return ExecuteReturnCmd(cmd );
        }

        public DataSet getForumPostsSample() 
        {
	        string strSql = "select * from ForumPosts where categoryid = 33 order by CreateDate";
	        //string strSql = "select * from ForumPosts where postid =134";
	        SqlCommand cmd = new SqlCommand(strSql.ToString());

	        return ExecuteReturnCmd(cmd );
        }

		public DataSet GetTopPartners(int topNumber) 
		{
			string strSql = "select top " + topNumber + 
                           " p.partnercode,p.URL,fp.partnerid, count(*) NumberOfArticles " +  
                           " from forumposts fp " +
                           " join partner p on fp.partnerid = p.partnerid " +
                           " where fp.userid = 168 and p.URL not like '%springofwater%' " + 
                           " group by fp.partnerid,p.partnercode,p.URL " +
                           " order by NumberOfArticles desc ";
			SqlCommand cmd = new SqlCommand(strSql.ToString());
			return ExecuteReturnCmd(cmd );
		}

        public DataSet GetPostAndRepliesByPostID(long postID)
        {

            //string strSql = "select Subject, Body, nickname  from forumposts fp  left join users u  on fp.UserID = u.UserID where postid = " + postID +
            //                "union ALL " +
            //                "select  Subject, Body, nickname  from forumposts fp  left join users u  on fp.UserID = u.UserID where parentpostid = " + postID;
            // SqlCommand cmd = new SqlCommand(strSql.ToString());

            string storeProcedureName = "springofwat.sp_getPostAndRepliesByPostID";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter postIDPara = cmd.CreateParameter();
            postIDPara.SqlDbType = SqlDbType.Int;
            postIDPara.ParameterName = "@PostID";
            postIDPara.Value = postID;
            cmd.Parameters.Add(postIDPara);

            return ExecuteReturnCmd(cmd);
        }

        public DataSet getPosts(int userID, int year, int month, int day, 
			                int startIndex, int numberOfRecords, out int totalRecords,
				        string type, string status, int categoryID, string orderBy) 
        {
	       return getForumPosts(userID, year, month, day, 
			startIndex, numberOfRecords, out totalRecords,
			type, status, categoryID, 0,orderBy) ;
        }

		public DataSet getForumPosts(int userID, int year, int month, int day, 
			int startIndex, int numberOfRecords, out int totalRecords,
			string type, string status, int categoryID, int partnerID,string orderBy) 
		{
			string storeProcedureName = "springofwat.sp_getForumPosts";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter userPara = cmd.CreateParameter();
			userPara.SqlDbType = SqlDbType.Int;
			userPara.ParameterName = "@UserID";
			userPara.Value = userID;
			cmd.Parameters.Add(userPara);

			SqlParameter yearPara = cmd.CreateParameter();
			yearPara.SqlDbType = SqlDbType.Int;
			yearPara.ParameterName = "@Year";
			yearPara.Value = year;
			cmd.Parameters.Add(yearPara);

			SqlParameter monthPara = cmd.CreateParameter();
			monthPara.SqlDbType = SqlDbType.Int;
			monthPara.ParameterName = "@Month";
			monthPara.Value = month;
			cmd.Parameters.Add(monthPara);

			SqlParameter dayPara = cmd.CreateParameter();
			dayPara.SqlDbType = SqlDbType.Int;
			dayPara.ParameterName = "@Day";
			dayPara.Value = day;
			cmd.Parameters.Add(dayPara);

			SqlParameter startPara = cmd.CreateParameter();
			startPara.SqlDbType = SqlDbType.Int;
			startPara.ParameterName = "@StartIndex";
			startPara.Value = startIndex;
			cmd.Parameters.Add(startPara);

			SqlParameter numPara = cmd.CreateParameter();
			numPara.SqlDbType = SqlDbType.Int;
			numPara.ParameterName = "@NumberOfRecords";
			numPara.Value = numberOfRecords;
			cmd.Parameters.Add(numPara);

			SqlParameter totalPara = cmd.CreateParameter();
			totalPara.SqlDbType = SqlDbType.Int;
			totalPara.ParameterName = "@TotalRecords";
			totalPara.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(totalPara);

			SqlParameter typePara = cmd.CreateParameter();
			typePara.SqlDbType = SqlDbType.VarChar;
			typePara.ParameterName = "@Type";
			typePara.Value = type;
			cmd.Parameters.Add(typePara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@Status";
			statusPara.Value = status;
			cmd.Parameters.Add(statusPara);
            
			SqlParameter catidPara = cmd.CreateParameter();
			catidPara.SqlDbType = SqlDbType.Int;
			catidPara.ParameterName = "@CategoryID";
			catidPara.Value = categoryID;
			cmd.Parameters.Add(catidPara);

			SqlParameter partnerIDPara = cmd.CreateParameter();
			partnerIDPara.SqlDbType = SqlDbType.Int;
			partnerIDPara.ParameterName = "@PartnerID";
			partnerIDPara.Value = partnerID;
			cmd.Parameters.Add(partnerIDPara);

			SqlParameter orderPara = cmd.CreateParameter();
			orderPara.SqlDbType = SqlDbType.VarChar;
			orderPara.ParameterName = "@OrderBy";
			orderPara.Value = orderBy;
			cmd.Parameters.Add(orderPara);

			DataSet ds = ExecuteReturnCmd(cmd);

			totalRecords = int.Parse(cmd.Parameters["@TotalRecords"].Value.ToString());

			return ds;
		}


        public DataSet getReplyCountsByUserID(int userID, string type, string status, int categoryID) 
        {
        string storeProcedureName = "springofwat.sp_GetReplyCounts";
        SqlCommand cmd = new SqlCommand(storeProcedureName);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter userPara = cmd.CreateParameter();
        userPara.SqlDbType = SqlDbType.Int;
        userPara.ParameterName = "@UserID";
        userPara.Value = userID;
        cmd.Parameters.Add(userPara);

        SqlParameter typePara = cmd.CreateParameter();
        typePara.SqlDbType = SqlDbType.VarChar;
        typePara.ParameterName = "@Type";
        typePara.Value = type;
        cmd.Parameters.Add(typePara);

        SqlParameter statusPara = cmd.CreateParameter();
        statusPara.SqlDbType = SqlDbType.VarChar;
        statusPara.ParameterName = "@Status";
        statusPara.Value = status;
        cmd.Parameters.Add(statusPara);

        SqlParameter catidPara = cmd.CreateParameter();
        catidPara.SqlDbType = SqlDbType.Int;
        catidPara.ParameterName = "@CategoryID";
        catidPara.Value = categoryID;
        cmd.Parameters.Add(catidPara);


        return ExecuteReturnCmd(cmd);
        }



        public DataSet getRepliesByUserID(int userID, int startIndex, int numberOfRecords, 
	        out int totalRecords, string type, string status, int categoryID, string orderBy) 
        {
	        string storeProcedureName = "springofwat.sp_getRepliesByUserID";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter userPara = cmd.CreateParameter();
	        userPara.SqlDbType = SqlDbType.Int;
	        userPara.ParameterName = "@UserID";
	        userPara.Value = userID;
	        cmd.Parameters.Add(userPara);

	        SqlParameter startPara = cmd.CreateParameter();
	        startPara.SqlDbType = SqlDbType.Int;
	        startPara.ParameterName = "@StartIndex";
	        startPara.Value = startIndex;
	        cmd.Parameters.Add(startPara);

	        SqlParameter numPara = cmd.CreateParameter();
	        numPara.SqlDbType = SqlDbType.Int;
	        numPara.ParameterName = "@NumberOfRecords";
	        numPara.Value = numberOfRecords;
	        cmd.Parameters.Add(numPara);

	        SqlParameter totalPara = cmd.CreateParameter();
	        totalPara.SqlDbType = SqlDbType.Int;
	        totalPara.ParameterName = "@TotalRecords";
	        totalPara.Direction = ParameterDirection.Output;
	        cmd.Parameters.Add(totalPara);

	        SqlParameter typePara = cmd.CreateParameter();
	        typePara.SqlDbType = SqlDbType.VarChar;
	        typePara.ParameterName = "@Type";
	        typePara.Value = type;
	        cmd.Parameters.Add(typePara);

	        SqlParameter statusPara = cmd.CreateParameter();
	        statusPara.SqlDbType = SqlDbType.VarChar;
	        statusPara.ParameterName = "@Status";
	        statusPara.Value = status;
	        cmd.Parameters.Add(statusPara);
            
	        SqlParameter catidPara = cmd.CreateParameter();
	        catidPara.SqlDbType = SqlDbType.Int;
	        catidPara.ParameterName = "@CategoryID";
	        catidPara.Value = categoryID;
	        cmd.Parameters.Add(catidPara);

	        SqlParameter orderPara = cmd.CreateParameter();
	        orderPara.SqlDbType = SqlDbType.VarChar;
	        orderPara.ParameterName = "@OrderBy";
	        orderPara.Value = orderBy;
	        cmd.Parameters.Add(orderPara);

	        DataSet ds = ExecuteReturnCmd(cmd);
            
	        totalRecords = int.Parse(cmd.Parameters["@TotalRecords"].Value.ToString());

	        return ds;
        }


        public DataSet getRepliesByPostID(int postID, int startIndex, int numberOfRecords, 
	        out int totalRecords, string type, string status, int categoryID, string orderBy) 
        {
	        string storeProcedureName = "springofwat.sp_getRepliesByPostID";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;

	        SqlParameter postIDPara = cmd.CreateParameter();
	        postIDPara.SqlDbType = SqlDbType.Int;
	        postIDPara.ParameterName = "@PostID";
	        postIDPara.Value = postID;
	        cmd.Parameters.Add(postIDPara);

	        SqlParameter startPara = cmd.CreateParameter();
	        startPara.SqlDbType = SqlDbType.Int;
	        startPara.ParameterName = "@StartIndex";
	        startPara.Value = startIndex;
	        cmd.Parameters.Add(startPara);

	        SqlParameter numPara = cmd.CreateParameter();
	        numPara.SqlDbType = SqlDbType.Int;
	        numPara.ParameterName = "@NumberOfRecords";
	        numPara.Value = numberOfRecords;
	        cmd.Parameters.Add(numPara);

	        SqlParameter totalPara = cmd.CreateParameter();
	        totalPara.SqlDbType = SqlDbType.Int;
	        totalPara.ParameterName = "@TotalRecords";
	        totalPara.Direction = ParameterDirection.Output;
	        cmd.Parameters.Add(totalPara);

	        SqlParameter typePara = cmd.CreateParameter();
	        typePara.SqlDbType = SqlDbType.VarChar;
	        typePara.ParameterName = "@Type";
	        typePara.Value = type;
	        cmd.Parameters.Add(typePara);

	        SqlParameter statusPara = cmd.CreateParameter();
	        statusPara.SqlDbType = SqlDbType.VarChar;
	        statusPara.ParameterName = "@Status";
	        statusPara.Value = status;
	        cmd.Parameters.Add(statusPara);
            
	        SqlParameter catidPara = cmd.CreateParameter();
	        catidPara.SqlDbType = SqlDbType.Int;
	        catidPara.ParameterName = "@CategoryID";
	        catidPara.Value = categoryID;
	        cmd.Parameters.Add(catidPara);

	        SqlParameter orderPara = cmd.CreateParameter();
	        orderPara.SqlDbType = SqlDbType.VarChar;
	        orderPara.ParameterName = "@OrderBy";
	        orderPara.Value = orderBy;
	        cmd.Parameters.Add(orderPara);

	        DataSet ds = ExecuteReturnCmd(cmd);

	        totalRecords = int.Parse(cmd.Parameters["@TotalRecords"].Value.ToString());

	        return ds;
        }

        public DataSet getUsers(string userType, string postType) 
        {
	        string storeProcedureName = "springofwat.sp_GetUsers";
	        SqlCommand cmd = new SqlCommand(storeProcedureName);
	        cmd.CommandType = CommandType.StoredProcedure;
            
	        SqlParameter userTypePara = cmd.CreateParameter();
	        userTypePara.SqlDbType = SqlDbType.VarChar;
	        userTypePara.ParameterName = "@UserType";
	        userTypePara.Value = userType;
	        cmd.Parameters.Add(userTypePara);

	        SqlParameter postTypePara = cmd.CreateParameter();
	        postTypePara.SqlDbType = SqlDbType.VarChar;
	        postTypePara.ParameterName = "@PostType";
	        postTypePara.Value = postType;
	        cmd.Parameters.Add(postTypePara);

	        return ExecuteReturnCmd(cmd);
        }

		//Created for edting ForumPost Table in admin tool--JerryC
		public DataSet getAllPosts() 
		{
			string sSQL = "Select PostID, Subject, CreateDate, Status, Summary,Type, PartnerID, OriginAuthor, " + 
				"URL, UserID, CreateUser From ForumPosts order by CreateDate Desc";
			//string strSql = "select * from ForumPosts where postid =134";
			SqlCommand cmd = new SqlCommand(sSQL);

			return ExecuteReturnCmd(cmd );
		}
		
		//Created for updating ForumPost Table in admin tool--JerryC
		public void UpdatePost(int postID, string type, string status, 
			string subject, string summary, int PartnerID, string OriginAuthor, string URL, string CreateUser)
		{
			
			
			string sSQL = "Update ForumPosts Set Subject =@Subject, " +
					"Type =@Type, Status =@Status, Summary =@Summary, " +
					"PartnerID = @PartnerID, OriginAuthor = @OriginAuthor," + 
					"URL = @URL, CreateUser=@CreateUser WHERE PostID = @PostID";
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter postIDPara = cmd.CreateParameter();
			postIDPara.SqlDbType = SqlDbType.Int;
			postIDPara.ParameterName = "@PostID";
			postIDPara.Value = postID;
			cmd.Parameters.Add(postIDPara);

			SqlParameter typePara = cmd.CreateParameter();
			typePara.SqlDbType = SqlDbType.VarChar;
			typePara.ParameterName = "@Type";
			typePara.Value = type;
			cmd.Parameters.Add(typePara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@Status";
			statusPara.Value = status;
			cmd.Parameters.Add(statusPara);

			SqlParameter subjectPara = cmd.CreateParameter();
			subjectPara.SqlDbType = SqlDbType.NVarChar;
			subjectPara.ParameterName = "@Subject";
			subjectPara.Value = subject;
			cmd.Parameters.Add(subjectPara);

			SqlParameter summaryPara = cmd.CreateParameter();
			summaryPara.SqlDbType = SqlDbType.NVarChar;
			summaryPara.ParameterName = "@Summary";
			summaryPara.Value = summary;
			cmd.Parameters.Add(summaryPara);

			SqlParameter partnerPara = cmd.CreateParameter();
			partnerPara.SqlDbType = SqlDbType.Int;
			partnerPara.ParameterName = "@PartnerID";
			partnerPara.Value = PartnerID;
			cmd.Parameters.Add(partnerPara);


			SqlParameter authorPara = cmd.CreateParameter();
			authorPara.SqlDbType = SqlDbType.NVarChar;
			authorPara.ParameterName = "@OriginAuthor";
			authorPara.Value = OriginAuthor;
			cmd.Parameters.Add(authorPara);

			SqlParameter URLPara = cmd.CreateParameter();
			URLPara.SqlDbType = SqlDbType.VarChar;
			URLPara.ParameterName = "@URL";
			URLPara.Value = URL;
			cmd.Parameters.Add(URLPara);

			SqlParameter CreatePara = cmd.CreateParameter();
			CreatePara.SqlDbType = SqlDbType.VarChar;
			CreatePara.ParameterName = "@CreateUser";
			CreatePara.Value = CreateUser;
			cmd.Parameters.Add(CreatePara);

			ExecuteNonReturnCmd(cmd);
		}

        //start of typingentry usage
        public void InsertAuthorAndWriting(string Email, string FirstName, string LastName,
                string Address, string City, string State, string Country, string ZipCode,
                string Phone, string Fax, string Church, string RecommendBy, string Title,
                int CategoryID, int SourcePostID, out int ReturnID)
        {
            string storeProcedureName = "dbo.sp_InsertAuthorAndWriting";
            SqlCommand cmd = new SqlCommand(storeProcedureName);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter EmailPara = cmd.CreateParameter();
            EmailPara.SqlDbType = SqlDbType.VarChar;
            EmailPara.ParameterName = "@Email";
            EmailPara.Value = Email;
            cmd.Parameters.Add(EmailPara);

            SqlParameter FirstNamePara = cmd.CreateParameter();
            FirstNamePara.SqlDbType = SqlDbType.VarChar;
            FirstNamePara.ParameterName = "@FirstName";
            FirstNamePara.Value = FirstName;
            cmd.Parameters.Add(FirstNamePara);

            SqlParameter LastNamePara = cmd.CreateParameter();
            LastNamePara.SqlDbType = SqlDbType.VarChar;
            LastNamePara.ParameterName = "@LastName";
            LastNamePara.Value = LastName;
            cmd.Parameters.Add(LastNamePara);

            SqlParameter AddressPara = cmd.CreateParameter();
            AddressPara.SqlDbType = SqlDbType.VarChar;
            AddressPara.ParameterName = "@Address";
            AddressPara.Value = Address;
            cmd.Parameters.Add(AddressPara);

            SqlParameter CityPara = cmd.CreateParameter();
            CityPara.SqlDbType = SqlDbType.VarChar;
            CityPara.ParameterName = "@City";
            CityPara.Value = City;
            cmd.Parameters.Add(CityPara);

            SqlParameter StatePara = cmd.CreateParameter();
            StatePara.SqlDbType = SqlDbType.VarChar;
            StatePara.ParameterName = "@State";
            StatePara.Value = State;
            cmd.Parameters.Add(StatePara);

            SqlParameter ZipCodePara = cmd.CreateParameter();
            ZipCodePara.SqlDbType = SqlDbType.VarChar;
            ZipCodePara.ParameterName = "@ZipCode";
            ZipCodePara.Value = ZipCode;
            cmd.Parameters.Add(ZipCodePara);

            SqlParameter CountryPara = cmd.CreateParameter();
            CountryPara.SqlDbType = SqlDbType.VarChar;
            CountryPara.ParameterName = "@Country";
            CountryPara.Value = Country;
            cmd.Parameters.Add(CountryPara);

            SqlParameter PhonePara = cmd.CreateParameter();
            PhonePara.SqlDbType = SqlDbType.VarChar;
            PhonePara.ParameterName = "@Phone";
            PhonePara.Value = Phone;
            cmd.Parameters.Add(PhonePara);

            SqlParameter FaxPara = cmd.CreateParameter();
            FaxPara.SqlDbType = SqlDbType.VarChar;
            FaxPara.ParameterName = "@Fax";
            FaxPara.Value = Fax;
            cmd.Parameters.Add(FaxPara);

            SqlParameter ChurchPara = cmd.CreateParameter();
            ChurchPara.SqlDbType = SqlDbType.VarChar;
            ChurchPara.ParameterName = "@Church";
            ChurchPara.Value = Church;
            cmd.Parameters.Add(ChurchPara);

            SqlParameter RecommendByPara = cmd.CreateParameter();
            RecommendByPara.SqlDbType = SqlDbType.VarChar;
            RecommendByPara.ParameterName = "@RecommendBy";
            RecommendByPara.Value = RecommendBy;
            cmd.Parameters.Add(RecommendByPara);

            SqlParameter TitlePara = cmd.CreateParameter();
            TitlePara.SqlDbType = SqlDbType.VarChar;
            TitlePara.ParameterName = "@Title";
            TitlePara.Value = Title;
            cmd.Parameters.Add(TitlePara);

            SqlParameter CategoryIDPara = cmd.CreateParameter();
            CategoryIDPara.SqlDbType = SqlDbType.VarChar;
            CategoryIDPara.ParameterName = "@CategoryID";
            CategoryIDPara.Value = CategoryID;
            cmd.Parameters.Add(CategoryIDPara);

            SqlParameter SourcePostIDPara = cmd.CreateParameter();
            SourcePostIDPara.SqlDbType = SqlDbType.VarChar;
            SourcePostIDPara.ParameterName = "@SourcePostID";
            SourcePostIDPara.Value = SourcePostID;
            cmd.Parameters.Add(SourcePostIDPara);

            ExecuteNonReturnCmd(cmd);
            @ReturnID = -1;
            //ReturnID = cmd.Parameters["@ReturnID"].Value();
            return;

            //DataSet ds = this.ExecuteReturnCmd(cmd);
            //ReturnID = int.Parse(cmd.Parameters["@ReturnID"].Value.ToString());
            //return ds;

            //DataSet ds = ExecuteReturnCmd(cmd);
            //ReturnID = int.Parse(cmd.Parameters["@ReturnID"].Value.ToString());
            //return ds;
        }
        //end of typingentry usage
    }
}
